prompt 费用登记中间表生成 SP_SF_FYDJ00_FYMX00
create or replace procedure SP_SF_FYDJ00_FYMX00
(
ad_CZY000 in number ,--输入:操作员
ad_BRID00 in number ,--输入:病人ID
ad_GHID00 in number ,--输入:挂号ID
as_GHH000 in varchar2 ,--输入:挂号号
ad_ID0000 in number ,--输入:id0000=SF_FYXX00_TEMPMX.ID0000
ad_DJH000 in number ,--输入:费用表的单据号
as_DJLSH0 in varchar2 ,--输入:单据流水号(处方流水号或医技单据号)
as_XMFL00 in varchar2 ,--输入:项目分类 0处方 1医技 2:费用 3:收费代入费用
ad_XMBH00 in number ,--输入:[SP_SF_FYMX00_REG000]医疗收费编码或药品编码
as_XMMC00 in varchar2 ,--输入:[SP_SF_FYMX00_REG000]项目名称
as_XMGG00 in varchar2 ,--输入:[SP_SF_FYMX00_REG000]药品规格
as_XMDW00 in varchar2 ,--输入:[SP_SF_FYMX00_REG000]单位
ad_XMDJ00 in number ,--输入:[SP_SF_FYMX00_REG000]单价
ad_XMSL00 in number ,--输入:[SP_SF_FYMX00_REG000]数量
ad_HJJE00 in number ,--输入:[SP_SF_FYMX00_REG000]总金额
ad_ZFJE00 in number ,--输入:[SP_SF_FYMX00_REG000]自费金额
ad_JZJE00 in number ,--输入:[SP_SF_FYMX00_REG000]记账金额
ad_GFJE00 in number ,--输入:[SP_SF_FYMX00_REG000]公费金额
ad_QZFJE0 in number ,--输入:[SP_SF_FYMX00_REG000]自付金额
ad_KDKS00 in number ,--输入:[SP_SF_FYMX00_REG000]开单科室编号
ad_KDYS00 in number ,--输入:[SP_SF_FYMX00_REG000]开单医生工号
ad_ZXKS00 in number ,--输入:[SP_SF_FYMX00_REG000]执行科室编号
ad_ZXYS00 in number ,--输入:[SP_SF_FYMX00_REG000]执行医生工号
as_SFYDJ0 in varchar2 ,--输入:[SP_SF_FYMX00_REG000]是否收费处登记,'1':收费处登记
as_ZFBL00 in varchar2 ,--输入:[SP_SF_FYMX00_REG000]自付比例
as_ZRZYLS in varchar2 default '' ,--输入:择日住院流水号
ad_SFCKBH in number ,--输入:[SP_SF_YFCF00]收费窗口编号
ad_PYCKBH in number default 0 ,--输入:[SP_SF_YFCF00]
ad_FYCKBH in number default 0 ,--输入:[SP_SF_YFCF00]
as_SFQZF0 in varchar2 default 'N',--输入:[SP_SF_YFCF00]
as_ZYDJQT in varchar2 default 'N',--输入:[SP_SF_YFCF00]是否执行中药代煎其他费用（中药袋子费用、配送费用） Y：执行 N:不执行
as_COMMIT in varchar2 default 'N',--输入:是否提交事务 Y:是 N:否
as_INXML0 in varchar2 ,--输入:xml字符
ad_ZXZT00 out varchar2 ,--输出:执行状态 0:失败 1：成功
as_ERRMSG out varchar2 ,--输出:系统提示的错误信息
as_OUTXML out varchar2 --输出:xml字符
)
-- MODIFICATION HISTORY
-- Person Date Comments
--zhangyc 2023.06.25 参考SP_SF_FYMX00_REG000、SP_SF_YFCF00、SP_SF_YJDJ00 by MZSF-20230626-001
--zhangyc 2023.09.27 as_XMFL00=1时，ad_ZXKS00，ad_ZXYS00=YJ_YW0000.YJKSBH、KDYS00
as
LS_COUNT0 number(5);
LS_CZRQ00 char(8); --操作日期
LS_CZSJ00 char(8); --操作时间
LS_YBZXLB IC_YBBRLB.YBZXLB%type;
LS_YBLB00 IC_YBBRLB.YBLB00%type;
LS_FBBH00 BM_BRXXB0.FBBH00%type;
LS_SplitNo varchar2(1);--是否拆分收费单据号 1:拆分 0:不拆分
LS_OPEN_ZFCFBZ varchar2(1);--开启自费处方标志分开结算(0:不启用 1:启用)
SF_SplitFBillNo varchar2(1);--门诊收费系统费用处理登记未扣费是否独立生成收费单号(不同的医技单或处方流水号收费单据号分开) 0:不分开 1:分开 默认值:0
LS_ID0000 SF_FYXX00_DJH000.ID0000%type;--Id0000
LS_BRID00 SF_FYXX00_DJH000.BRID00%type;--病人ID
LS_GHID00 SF_FYXX00_DJH000.GHID00%type;--挂号id
LS_GHH000 SF_FYXX00_DJH000.GHH000%type;--挂号号
LS_DJH000 SF_FYXX00_DJH000.DJH000%type;--单据号
LS_TPCDJH SF_FYXX00_DJH000.TPCDJH%type;--同批次收费单据号
LS_CFLSH0 SF_FYXX00_DJH000.CFLSH0%type;--处方流水号
LS_YJDJH0 SF_FYXX00_DJH000.YJDJH0%type;--医技单据号
LS_XMFL00 SF_FYXX00_DJH000.XMFL00%type;--项目分类 0:处方 1:医技 2:费用 3:收费代入费用
LS_ZFCFBZ SF_FYXX00_DJH000.ZFCFBZ%type;--自费处方标志:0或空:正常处方，1:自费处方
LS_XMLSTR SF_FYXX00_DJH000.XMLSTR%type;--XML字符
LS_MXID00 SF_FYXX00_DJH000.MXID00%type;--明细ID=SF_FYXX00_TEMPMX.MXID00
LS_CFID00 SF_FYXX00_DJH000.CFID00%type;--处方ID或YJ_YWJJ00.ID0000
LS_KFZT00 SF_FYXX00_TEMPMX.KFZT00%type;--扣费状态0 未扣费 1已扣费
LS_JSLX00 SF_FYXX00_TEMPMX.JSLX00%type;--结算类型 0 费用登记 1费用结算
LS_SFFSYB SF_FYXX00_TEMPMX.SFFSYB%type;--是否发送医保 1发送 0:不发送
LS_SFYLXM SF_FYXX00_TEMPMX.SFYLXM%type;--是否医疗项目 Y:是否 N:否
LS_SFXZFS SF_FYXX00_TEMPMX.SFXZFS%type;--是否选择发送 0 否 1 是
LS_BAKXML SF_FYXX00_TEMPMX.BAKXML%type;--医保备用2
LS_YSKS00 SF_FYXX00_TEMPMX.YSKS00%type;
LS_KDKS00 SF_FYXX00_TEMPMX.KDKS00%type;--开单科室编号
LS_KDYS00 SF_FYXX00_TEMPMX.KDYS00%type;--开单医生工号
LS_ZXKS00 SF_FYXX00_TEMPMX.ZXKS00%type;--执行科室编号
LS_ZXYS00 SF_FYXX00_TEMPMX.ZXYS00%type;--执行医生工号
LS_YJKSBH YJ_YW0000.YJKSBH%type;--医技科室编码
LS_YJKDYS YJ_YW0000.KDYS00%type;--医技开单医生
begin
ad_ZXZT00:='0';--输出:执行状态 0:失败 1：成功
as_ERRMSG:='';--输出:系统提示的错误信息
as_OUTXML:=null;
LS_CZRQ00:=to_char(sysdate,'YYYYMMDD');
LS_CZSJ00:=to_char(sysdate,'HH24:MI:SS');
LS_SplitNo:='0';--是否拆分收费单据号 1:拆分 0:不拆分
LS_ID0000:=ad_ID0000;--Id0000
LS_BRID00:=ad_BRID00;--病人ID
LS_GHID00:=ad_GHID00;--挂号id
LS_GHH000:=as_GHH000;--挂号号
LS_XMFL00:=as_XMFL00;--项目分类 0:处方 1:医技 2:费用 3:收费代入费用
LS_DJH000:=nvl(ad_DJH000,0);--收费单据号
LS_TPCDJH:=nvl(ad_DJH000,0);--同批次收费单据号
LS_CFID00:=0;--处方ID或YJ_YWJJ00.ID0000
LS_XMLSTR:=null;--XML字符
LS_KFZT00:='0';--扣费状态0 未扣费 1已扣费
LS_JSLX00:='0';--结算类型 0 费用登记 1费用结算
LS_KDKS00:=ad_KDKS00;--开单科室编号
LS_KDYS00:=ad_KDYS00;--开单医生工号
LS_ZXKS00:=ad_ZXKS00;--执行科室编号
LS_ZXYS00:=ad_ZXYS00;--执行医生工号
begin
select BMBH00 into LS_YSKS00 from BM_YGBM00 where YGBH00 = LS_KDYS00;
exception
when others then
LS_YSKS00:=0;
end;
begin
select A.FBBH00,C.YBZXLB,C.YBLB00
into LS_FBBH00,LS_YBZXLB,LS_YBLB00
from BM_BRXXB0 A,IC_YBBRLB C
where A.FBBH00 =C.FBBH00 and A.YBLB00=C.YBLB00 and A.BRID00=LS_BRID00;
exception
when others then
ad_ZXZT00:='0';
as_ERRMSG:='未找到有效病人基本信息！BRID00='||LS_BRID00;
rollback;
return;
end;
if LS_XMFL00 in ('2','3') then --2:费用 3:收费代入费用
select count(1) into LS_COUNT0 from BM_FPXM00 A,BM_YYSFXM B where A.FPXMID = B.MZFPID and B.SFXMID=ad_XMBH00;
if nvl(LS_COUNT0,0) = 0 then
ad_ZXZT00:='0';
as_ERRMSG:='未找到有效的医院收费项目！SFXMID='||ad_XMBH00;
rollback;
return;
end if;
end if;
--步骤0:入参组成xml格式
if LS_XMFL00 in ('0') then --0:处方
LS_CFLSH0:=as_DJLSH0;--处方流水号
LS_YJDJH0:=0;--医技单据号
LS_XMLSTR:=null;--XML字符
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AS_CFLSH0',as_DJLSH0,0,0,null,null),1,4000);--as_CFLSH0 in char, --处方流水号
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AD_DJH000',ad_DJH000,0,0,null,null),1,4000);--ad_DJH000 in number, --单据号
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AD_SFCKBH',ad_SFCKBH,0,0,null,null),1,4000);--ad_SFCKBH in number, --收费窗口编号
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AD_PYCKBH',ad_PYCKBH,0,0,null,null),1,4000);--ad_PYCKBH in number default 0,
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AD_FYCKBH',ad_FYCKBH,0,0,null,null),1,4000);--ad_FYCKBH in number default 0,
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AS_SFQZF0',as_SFQZF0,0,0,null,null),1,4000);--as_SFQZF0 in char default 'N',
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AD_CZY000',ad_CZY000,0,0,null,null),1,4000);--ad_CZY000 in number default 0, --操作员
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AS_ZYDJQT',AS_ZYDJQT,0,0,null,null),1,4000);--AS_ZYDJQT in char default 'N' --是否执行中药代煎其他费用（中药袋子费用、配送费用） Y：执行 N:不执行
LS_XMLSTR:=substrb(''||LS_XMLSTR||'',1,4000);
elsif LS_XMFL00 in ('1') then -- 1:医技
LS_CFLSH0:=null;--处方流水号
LS_YJDJH0:=SF_SF_STRTONUM(as_DJLSH0);--医技单据号
begin
select YJKSBH,KDYS00 into LS_YJKSBH,LS_YJKDYS from YJ_YW0000 where BRID00=ad_BRID00 and YJDJH0=LS_YJDJH0;
exception
when others then
LS_YJKSBH:=null;--医技科室编码
LS_YJKDYS:=null;--医技开单医生
end;
LS_ZXKS00:=nvl(LS_YJKSBH,ad_ZXKS00);--执行科室编号
LS_ZXYS00:=nvl(LS_YJKDYS,ad_ZXYS00);--执行医生工号
LS_XMLSTR:=null;--XML字符
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AD_YJDJH0',as_DJLSH0,0,0,null,null),1,4000);--ad_YJDJH0 IN NUMBER, --医技单据号
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AD_DJH000',ad_DJH000,0,0,null,null),1,4000);--ad_DJH000 IN NUMBER, --单据号
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AD_ZXKS00',LS_ZXKS00,0,0,null,null),1,4000);--ad_ZXKS00 IN NUMBER, --执行科室
LS_XMLSTR:=substrb(LS_XMLSTR||SF_XT_XMLJSON('AD_ZXYS00',LS_ZXYS00,0,0,null,null),1,4000);--ad_ZXYS00 IN NUMBER, --执行医生
LS_XMLSTR:=substrb(''||LS_XMLSTR||'',1,4000);
elsif LS_XMFL00 in ('2') then --2:费用
LS_CFLSH0:=null;--处方流水号
LS_YJDJH0:=0;--医技单据号
LS_XMLSTR:=null;--XML字符
elsif LS_XMFL00 in ('3') then --3:收费代入费用
LS_CFLSH0:='0';--处方流水号
LS_YJDJH0:=0;--医技单据号
LS_XMLSTR:=null;--XML字符
end if;
begin
select substr(trim(VALUE0),1,1) into SF_SplitFBillNo from XT_XTCS00 where name00 = 'SF_SplitFBillNo';
exception
when others then
SF_SplitFBillNo:='0';
end;
LS_OPEN_ZFCFBZ:=substrb(SF_SF_OPEN_ZFCFBZ(LS_BRID00,LS_FBBH00),1,1);--开启自费处方标志分开结算(0:不启用 1:启用)
if LS_OPEN_ZFCFBZ='1' or SF_SplitFBillNo='1' then
LS_SplitNo:='1';--是否拆分收费单据号 1:拆分 0:不拆分
end if;
--步骤1:获取中间收费单号
begin
select count(1),max(DJH000),max(MXID00),max(ZFCFBZ) into LS_COUNT0,LS_DJH000,LS_MXID00,LS_ZFCFBZ
from SF_FYXX00_DJH000 where ID0000=LS_ID0000 and BRID00=LS_BRID00
and GHID00=LS_GHID00 and XMFL00=LS_XMFL00
and
(
(LS_XMFL00 in ('0') and CFLSH0=LS_CFLSH0) --0:处方
or (LS_XMFL00 in ('1') and YJDJH0=LS_YJDJH0) --1:医技
or (LS_XMFL00 in ('2') and CFLSH0=LS_CFLSH0 and YJDJH0=LS_YJDJH0) --2:费用
or (LS_XMFL00 in ('3') and CFLSH0=LS_CFLSH0 and YJDJH0=LS_YJDJH0) --3:收费代入费用
);
exception
when others then
LS_DJH000:=null;
LS_MXID00:=null;--明细ID=SF_FYXX00_TEMPMX.MXID00
LS_ZFCFBZ:=null;--自费处方标志:0或空:正常处方，1:自费处方
end;
LS_DJH000:=nvl(LS_DJH000,ad_DJH000);
if LS_XMFL00 in ('0') then --0:处方
Select
substrb(SF_SF_GET_ZFCFBZ(0,A.BRID00,A.GHID00,A.DJH000,A.CFLSH0,A.BCCFH0,A.ZFCFBZ,A.FJFDJH,null),1,1) as ZFCFBZ --自费处方标志:0:正常处方 1:自费处方
into LS_ZFCFBZ
from YF_MZCF00 A where A.BRID00=LS_BRID00 and A.GHID00=LS_GHID00 and A.cflsh0=LS_CFLSH0;
elsif LS_XMFL00 in ('1') then -- 1:医技
select
substrb(SF_SF_GET_ZFCFBZ(1,A.BRID00,A.ZYGHID,A.SFDJH0,A.YJDJH0,A.CXDJH0,A.ZFCFBZ,A.ZLDCCF,null),1,1) as ZFCFBZ --自费处方标志:0:正常处方 1:自费处方
into LS_ZFCFBZ
from YJ_YW0000 A where A.BRID00=LS_BRID00 and A.ZYGHID=LS_GHID00 and A.YJDJH0 =LS_YJDJH0 and A.MZZYBZ='0' ;
elsif LS_XMFL00 in ('2') then --2:费用
LS_ZFCFBZ:=nvl(LS_ZFCFBZ,'0');--自费处方标志:0或空:正常处方，1:自费处方
elsif LS_XMFL00 in ('3') then --3:收费代入费用
LS_ZFCFBZ:=nvl(LS_ZFCFBZ,'0');--自费处方标志:0或空:正常处方，1:自费处方
end if;
--步骤2:中间表插入数据
if nvl(LS_COUNT0,0)=0 then
select SQ_SF_FYTEMP_MXID00.nextval into LS_MXID00 from dual;
if LS_SplitNo='1' then --是否拆分收费单据号 1:拆分 0:不拆分
select SQ_SF_BRJFB0_DJH000.nextval into LS_DJH000 from dual;
end if;
insert into SF_FYXX00_DJH000(ID0000,MXID00,BRID00,GHID00,GHH000,CFLSH0,CFID00,YJDJH0,DJH000,XMFL00,YPNM00,SFXMID,CFBZ00,KFZT00,JZDH00,ZFCFBZ,TPCDJH,XMLSTR)
select LS_ID0000,LS_MXID00,LS_BRID00,LS_GHID00,LS_GHH000,LS_CFLSH0,LS_CFID00,LS_YJDJH0,LS_DJH000,LS_XMFL00,0,0,LS_XMFL00,LS_KFZT00,0,LS_ZFCFBZ,LS_TPCDJH,LS_XMLSTR from dual;
end if;
--步骤三：插入中间表
if LS_FBBH00=3 then
if LS_ZFCFBZ='1' then
LS_SFFSYB :='0';--是否发送医保 1发送 0:不发送
LS_SFYLXM :='N';--是否医疗项目 Y:是否 N:否
LS_SFXZFS :='1';--是否选择发送 0 否 1 是
else
LS_SFFSYB :='1';--是否发送医保 1发送 0:不发送
LS_SFYLXM :='Y';--是否医疗项目 Y:是否 N:否
LS_SFXZFS :='0';--是否选择发送 0 否 1 是
end if;
else
LS_SFFSYB :='0';--是否发送医保 1发送 0:不发送
LS_SFYLXM :='N';--是否医疗项目 Y:是否 N:否
LS_SFXZFS :='0';--是否选择发送 0 否 1 是
end if;
if LS_XMFL00 in ('2','3') then --2:费用 3:收费代入费用
insert into SF_FYXX00_TEMPMX
(
ID0000,MXID00,CZY000,CZRQ00,CZSJ00,BRID00,FBBH00,YBZXLB,YBLB00,
GHID00,GHH000,XMFL00,ZFCFBZ,CFLSH0,CFID00,YJDJH0,DJH000,FLAG00,
SFXMID,YPNM00,XMBH00,XMMC00,XMDW00,XMGG00,XMJE00,XMSL00,XMDJ00,
JZJE00,ZFJE00,GFJE00,XJZF00,ZFBL00,KDKS00,KDYS00,ZXKS00,ZXYS00,
ZRZYLS,SFYDJ0,CFBZ00,KFZT00,JSLX00,SFFSYB,SFYLXM,SFXZFS,BAKXML,
YSKS00
)
select
LS_ID0000,LS_MXID00,ad_CZY000,LS_CZRQ00,LS_CZSJ00,LS_BRID00,LS_FBBH00,LS_YBZXLB,LS_YBLB00,
LS_GHID00,LS_GHH000,LS_XMFL00,LS_ZFCFBZ,LS_CFLSH0,LS_CFID00,LS_YJDJH0,LS_DJH000,decode(LS_XMFL00,'0','1','0'),
decode(LS_XMFL00,'0',0,ad_XMBH00),decode(LS_XMFL00,'0',ad_XMBH00,0),ad_XMBH00,substrb(trim(as_XMMC00),1,200),substrb(trim(as_XMDW00),1,50),substrb(trim(as_XMGG00),1,50),ad_HJJE00,ad_XMSL00,ad_XMDJ00,
ad_JZJE00,ad_ZFJE00,ad_GFJE00,ad_QZFJE0,as_ZFBL00,LS_KDKS00,LS_KDYS00,LS_ZXKS00,LS_ZXYS00,
as_ZRZYLS,as_SFYDJ0,LS_XMFL00,LS_KFZT00,LS_JSLX00,LS_SFFSYB,LS_SFYLXM,LS_SFXZFS,LS_BAKXML,
LS_YSKS00
from dual;
end if;
ad_ZXZT00:='1';--输出:执行状态 0:失败 1：成功
as_ERRMSG:='';--输出:系统提示的错误信息
as_OUTXML:='';
if as_COMMIT='Y' then
commit;
end if;
exception
when no_data_found then
ad_ZXZT00:='0';
as_ERRMSG:=substrb('门诊费用登记处理整理数据异常!错误原因:'||sqlerrm,1,200);
rollback;
when others then
ad_ZXZT00:='0';
as_ERRMSG:=substrb('门诊费用登记处理整理数据异常!错误原因:'||sqlerrm,1,200);
rollback;
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%